package dao.impl.StoreDaoImpl;

import common.model.Entity.StoreEntity.Product;
import dao.StoreDao.ProductDao;
import common.util.DBUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

/**
 * 商品数据访问接口实现类，提供商品相关的数据库操作实现。
 *
 * <p>所有“删除”均为逻辑删除（is_deleted = 1）。
 * 查询操作均默认过滤已逻辑删除的记录。
 *
 * @author your-name
 * @since 1.0
 */
public class ProductDaoImpl implements ProductDao {

    private static final Logger logger = LoggerFactory.getLogger(ProductDaoImpl.class);
    private final QueryRunner queryRunner = new QueryRunner();

    /** 查询时使用的通用字段列表，避免手写遗漏。 */
    private static final String PRODUCT_COLUMNS =
            "p.id, p.product_name AS productName, p.price, p.stock, p.description, " +
                    "p.category_id AS categoryId, p.status, p.image_url AS imageUrl, " +
                    "p.is_deleted AS isDeleted, p.created_at AS createdAt, p.updated_at AS updatedAt, " +
                    "c.category_name AS categoryName";

    /**
     * 根据商品名称精确查询未被逻辑删除的商品。
     *
     * @param name 商品名称（完全匹配）
     * @return 若存在返回 {@link Product}，否则返回 {@link Optional#empty()}
     */
    @Override
    public Optional<Product> findByProductName(String name) {
        String sql = "SELECT " + PRODUCT_COLUMNS +
                " FROM t_product p " +
                "LEFT JOIN t_category c ON p.category_id = c.id " +
                "WHERE p.product_name = ? AND p.is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            Product product = queryRunner.query(conn, sql, new BeanHandler<>(Product.class), name);
            return Optional.ofNullable(product);
        } catch (SQLException e) {
            logger.error("根据产品名称查询产品失败: name={}", name, e);
            return Optional.empty();
        }
    }

    /**
     * 根据分类 ID 查询未被逻辑删除的商品列表，按创建时间倒序排列。
     *
     * @param categoryId 分类主键
     * @return 商品列表；若未查询到数据或发生异常，返回空列表
     */
    @Override
    public List<Product> findByCategoryId(long categoryId) {
        String sql = "SELECT " + PRODUCT_COLUMNS +
                " FROM t_product p " +
                "LEFT JOIN t_category c ON p.category_id = c.id " +
                "WHERE p.category_id = ? AND p.is_deleted = 0 " +
                "ORDER BY p.created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Product.class), categoryId);
        } catch (SQLException e) {
            logger.error("根据分类ID查询产品失败: categoryId={}", categoryId, e);
            return List.of();
        }
    }

    /**
     * 根据分类名称查询未被逻辑删除的商品列表，按创建时间倒序排列。
     *
     * @param categoryName 分类名称（完全匹配）
     * @return 商品列表；若未查询到数据或发生异常，返回空列表
     */
    @Override
    public List<Product> findByCategoryName(String categoryName) {
        String sql = "SELECT " + PRODUCT_COLUMNS +
                " FROM t_product p " +
                "JOIN t_category c ON p.category_id = c.id " +
                "WHERE c.category_name = ? AND p.is_deleted = 0 " +
                "ORDER BY p.created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Product.class), categoryName);
        } catch (SQLException e) {
            logger.error("根据分类名称查询产品失败: categoryName={}", categoryName, e);
            return List.of();
        }
    }

    /**
     * 根据主键查询未被逻辑删除的商品。
     *
     * @param id 商品主键
     * @return 若存在返回 {@link Product}，否则返回 {@link Optional#empty()}
     */
    @Override
    public Optional<Product> findById(Long id) {
        String sql = "SELECT " + PRODUCT_COLUMNS +
                " FROM t_product p " +
                "LEFT JOIN t_category c ON p.category_id = c.id " +
                "WHERE p.id = ? AND p.is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            Product product = queryRunner.query(conn, sql, new BeanHandler<>(Product.class), id);
            return Optional.ofNullable(product);
        } catch (SQLException e) {
            logger.error("根据ID查询产品失败: id={}", id, e);
            return Optional.empty();
        }
    }

    /**
     * 查询所有未被逻辑删除的商品，按创建时间倒序排列。
     *
     * @return 商品列表；若未查询到数据或发生异常，返回空列表
     */
    @Override
    public List<Product> findAll() {
        String sql = "SELECT " + PRODUCT_COLUMNS +
                " FROM t_product p " +
                "LEFT JOIN t_category c ON p.category_id = c.id " +
                "WHERE p.is_deleted = 0 " +
                "ORDER BY p.created_at DESC";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new BeanListHandler<>(Product.class));
        } catch (SQLException e) {
            logger.error("查询所有产品失败", e);
            return List.of();
        }
    }

    /**
     * 逻辑删除指定主键的商品，并将 updated_at 置为当前时间。
     *
     * @param id 商品主键
     * @return 若受影响行数 > 0 返回 true，否则返回 false
     */
    @Override
    public boolean deleteById(Long id) {
        String sql = "UPDATE t_product SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql, id);
            return affected > 0;
        } catch (SQLException e) {
            logger.error("删除产品失败: id={}", id, e);
            return false;
        }
    }

    /**
     * 保存新商品信息。
     *
     * <p>仅插入非空业务字段，创建时间由数据库默认值填充。
     *
     * @param product 待保存的商品实体
     * @return 若受影响行数 > 0 返回 true，否则返回 false
     */
    @Override
    public boolean save(Product product) {
        String sql = "INSERT INTO t_product (product_name, description, category_id, price, stock, image_url, status) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql,
                    product.getProductName(),
                    product.getDescription(),
                    product.getCategoryId(),
                    product.getPrice(),
                    product.getStock(),
                    product.getImageUrl(),
                    product.getStatus());
            return affected > 0;
        } catch (SQLException e) {
            logger.error("保存产品失败: {}", product.getProductName(), e);
            return false;
        }
    }

    /**
     * 更新已有商品信息（仅更新未逻辑删除的记录）。
     *
     * <p>会自动将 updated_at 置为当前时间。
     *
     * @param product 待更新的商品实体，主键 id 不能为空
     * @return 若受影响行数 > 0 返回 true，否则返回 false
     */
    @Override
    public boolean update(Product product) {
        String sql = "UPDATE t_product SET product_name = ?, description = ?, category_id = ?, price = ?, stock = ?, " +
                "image_url = ?, status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ? AND is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            int affected = queryRunner.update(conn, sql,
                    product.getProductName(),
                    product.getDescription(),
                    product.getCategoryId(),
                    product.getPrice(),
                    product.getStock(),
                    product.getImageUrl(),
                    product.getStatus(),
                    product.getId());
            return affected > 0;
        } catch (SQLException e) {
            logger.error("更新产品失败: id={}", product.getId(), e);
            return false;
        }
    }

    /**
     * 统计未被逻辑删除的商品总数。
     *
     * @return 商品数量；若发生异常返回 0
     */
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) FROM t_product WHERE is_deleted = 0";
        try (Connection conn = DBUtil.getConnection()) {
            return queryRunner.query(conn, sql, new ScalarHandler<>());
        } catch (SQLException e) {
            logger.error("统计产品数量失败", e);
            return 0;
        }
    }
}